package com.suez.nullpointerexception.blizzard.impl;

import java.lang.reflect.Field;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import org.junit.Test;

import com.suez.nullpointerexception.blizzard.dao.PersonDao;
import com.suez.nullpointerexception.blizzard.entity.Person;

public class PersonDaoImpl implements PersonDao{

	@Override
	public List<Person> getAll() {
		String sql = "select * from PERSON";
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		List<Person> persons = new ArrayList<>();
		try {
			conn = DBUtil.getConnection();
			ps= conn.prepareStatement(sql);
			rs = ps.executeQuery();
			while(rs.next()){
				Person p = new Person();
				p.setBirthday(rs.getDate("BIRTHDAY"));
				p.setTel(rs.getString("TEL"));
				p.setPid(rs.getInt("PID"));
				p.setName(rs.getString("NAME"));
				p.setDid(rs.getInt("DID"));
				p.setSalary(rs.getDouble("SALARY"));
				persons.add(p);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		DBUtil.free(conn, ps, rs);
		return persons;
	}

	@Override
	public boolean insert(Person p) {
		
		StringBuilder sb = new StringBuilder("insert into person values(PERSON_SEQ.NEXTVAL,");
		sb.append("'").append(p.getName()).append("'").append(",");
		sb.append("to_date(").append("'").append(p.getBirthday().toString()).append("'").append(",").append("'yyyy-MM-dd'").append("),");
		sb.append("'").append(p.getTel()).append("'").append(",");
		sb.append(p.getDid()).append(",");
		sb.append(p.getSalary()).append(")");
		System.out.println("insert person sql:"+sb);
		//String sql = "insert into person values(PERSON_SEQ.NEXTVAL"+","+"'"+p.getName()+"'"+","+"to_date("+"'"+p.getBirthday().toString()+"'"+","+"'yyyy-MM-dd')"+","+"'"+p.getTel()+"'"+")";
		//String sql = "insert into person values(PERSON_SEQ.NEXTVAL"+","+"'"+p.getPname()+"'"+","+p.getBirthday()+","+"'"+p.getTel()+"'"+")";
		Connection conn = null;
		PreparedStatement ps = null;
		int len = 0;
		try {
			conn = DBUtil.getConnection();
			ps= conn.prepareStatement(sb.toString());
			len = ps.executeUpdate();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		DBUtil.free(conn, ps, null);
		return len==0?false:true;
	}

	@Override
	public boolean update(Person p) {
		StringBuilder sb = new StringBuilder("UPDATE PERSON SET ");
		Field []fields = p.getClass().getDeclaredFields();
		for(int i=0;i<fields.length;i++){
			Object o;
			Field f = fields[i];
			try {
				f.setAccessible(true);
				o = f.get(p);
				if(o!=null){
					String fieldName = f.getName();
					
					if(o instanceof java.sql.Date){
						sb.append(fieldName);
						sb.append("=");
						sb.append("to_date(").append("'").append(p.getBirthday().toString()).append("'").append(",").append("'yyyy-MM-dd'").append(")");
						//sb.append(p.getBirthday());
						sb.append(",");
					} 
					else if(!fieldName.equals("pid")&&!fieldName.equals("serialVersionUID")){
						if(o instanceof Integer){
							if(!o.equals(new Integer(0))){
								sb.append(fieldName).append("=").append(o).append(",");
							}
						}else if(o instanceof Double){
							if(!o.equals(new Double(0))){
								sb.append(fieldName).append("=").append(o).append(",");
							}
								
						}else{
							sb.append(fieldName);
							sb.append("=");
							sb.append("'");
							sb.append(o);
							sb.append("'");
							sb.append(",");
						}
						
						
					}
					
				}
			} catch (IllegalArgumentException | IllegalAccessException e) {
				e.printStackTrace();
			}
			
		}
		
		sb.deleteCharAt(sb.length()-1);
		
		sb.append(" WHERE PID = ").append(p.getPid());
		
		System.out.println(sb);
		Connection conn = null;
		PreparedStatement ps = null;
		int len = 0;
		try {
			conn = DBUtil.getConnection();
			ps= conn.prepareStatement(sb.toString());
			len = ps.executeUpdate();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		DBUtil.free(conn, ps, null);
		return len==0?false:true;
	}

	@Override
	public boolean delete(Person p) {
		String sql = "delete person where pid = "+ p.getPid();
		Connection conn = null;
		PreparedStatement ps = null;
		int len = 0;
		try {
			conn = DBUtil.getConnection();
			ps= conn.prepareStatement(sql);
			len = ps.executeUpdate();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		DBUtil.free(conn, ps, null);
		return len==0?false:true;
	}
	/*
	public static void main(String[] args) {
		Person p = new Person();
		p.setBirthday(java.sql.Date.valueOf("1910-10-10"));
		p.setPid(6);
		p.setPname("James");
		p.setTel("22222");
		PersonDaoImpl personDaoImpl = new PersonDaoImpl();
		System.out.println(personDaoImpl.insert(p));
	}
	
	*/
	@Test
	public void test(){
		Person p = new Person();
		p.setBirthday(java.sql.Date.valueOf("2012-03-10"));
		p.setPid(5);
		//p.setSalary(300.);
		p.setDid(3);
		this.update(p);
		//p.setName("kieren");
//		this.update(p);
//		p.setName("James");
		p.setDid(1);
//		p.setSalary(200.0);
		p.setTel("123");
//		p.setTel("22222");
//		p.setDid(3);
		//this.update(p);
		//this.update(p);
		//p.setDid(1);
		//p.setSalary(0.0);
		//this.update(p);
		System.out.println(this.getAll());
		
		
		
	}
	
	
	

}

